﻿using AchieveCommon;
using AchieveDALFactory;
using AchieveEntity;
using AchieveInterfaceDAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace AchieveBLL
{
    public class ProduceBLL
    {
        public string GetJsonPager(string tableName, string columns, string order, int pageSize, int pageIndex, string where, out int totalCount)
        {
            DataTable dt = AchieveCommon.SqlPagerHelper.GetPager(tableName, columns, order, pageSize, pageIndex, where, out totalCount);
            return AchieveCommon.JsonHelper.ToJson(dt);
        }
        
        /// <summary>
        /// 查询加急订单；返回：allNum，rushNum，dept
        /// </summary>
        /// <param name="year"></param>
        /// <returns></returns>
        public static DataTable GetRushMo(int year=2019)
        {
            // declare @year int
            //set @year=2018
            string sql = @"
                   select rtrim(isnull(dept,'其他')) as dept,sum(a) as allNum,sum(b)as rushNum, ROUND(CAST(sum(b) AS FLOAT)/sum(a)* 100,2) as ratio    from
                    (
                    select count(*) as a,b=0,
                    case FHeadSelfJ0181 when '0' then null else FHeadSelfJ0181 end   as dept from ICMO where  DATEPART(yyyy,FCommitDate)=@year group by(FHeadSelfJ0181)
                    UNION 
                    select a=0,count(*) as b,case FHeadSelfJ0181 when '0' then null else FHeadSelfJ0181 end   as dept from ICMO where  FWORKTYPEID = 68 and DATEPART(yyyy,FCommitDate)=@year group by(FHeadSelfJ0181)
                    ) as t  group by(dept) order by allNum desc";
   
            SqlParameter[] paras = { new SqlParameter("@year", SqlDbType.Int, 10), };
            paras[0].Value = year;
            try
            {
                return SqlHelper.GetDataTable(SqlHelper.connStrK3,CommandType.Text,sql,paras);
            }
            catch (Exception)
            {
                
                throw;
            }
            
        }
        /// <summary>
        /// 根据工艺名获取工艺
        /// </summary>
        public OperEntity GetOperByOperNote(string OperNote)
        {
            string sql = "select top 1  OperID,OperNote,DayTime,WeekTime,MonthTime,UpdateTime,UpdateBy,Remark from tbOper where OperNote = @OperNote";
            OperEntity oper = null;
            DataTable dt = SqlHelper.GetDataTable(SqlHelper.connStr, CommandType.Text, sql, new SqlParameter("@OperNote", OperNote));
            if (dt.Rows.Count > 0)
            {
                oper = new OperEntity();
                DataRowToModel(oper, dt.Rows[0]);
                return oper;
            }
            else
                return null;
        }

        /// <summary>
        /// 添加工艺
        /// </summary>
        public int AddOper(OperEntity Oper)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tbOper([OperID],[OperNote],[DayTime],[WeekTime],[MonthTime],UpdateTime,Remark,UpdateBy)");
            strSql.Append(" values ");
            strSql.Append("(@OperID,@OperNote,@DayTime,@WeekTime,@MonthTime,@UpdateTime,@UpdateBy,@Remark)");
            strSql.Append(";SELECT @@IDENTITY");   //返回插入用户的主键
            SqlParameter[] paras = { 
                                   new SqlParameter("@OperID",SqlDbType.VarChar,50),
                                   new SqlParameter("@OperNote",SqlDbType.VarChar,50),
                                   new SqlParameter("@DayTime",SqlDbType.Decimal),
                                   new SqlParameter("@WeekTime",SqlDbType.Decimal),
                                   new SqlParameter("@MonthTime",SqlDbType.Decimal),
                                   new SqlParameter("@Remark",SqlDbType.NVarChar,100),
                                   new SqlParameter("@UpdateTime",SqlDbType.DateTime),
                                   new SqlParameter("@UpdateBy",SqlDbType.NVarChar,100)
                                   };
            paras[0].Value = Oper.OperID;
            paras[1].Value = Oper.OperNote;
            paras[2].Value = Oper.DayTime;
            paras[3].Value = Oper.WeekTime;
            paras[4].Value = Oper.MonthTime;
            paras[5].Value = Oper.Remark;
            paras[6].Value = Oper.UpdateTime;
            paras[7].Value = Oper.UpdateBy;
            return Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.connStr, CommandType.Text, strSql.ToString(), paras));
        }

    
        /// <summary>
        /// 把DataRow行转成实体类对象
        /// </summary>
        private void DataRowToModel(OperEntity model, DataRow dr)
        {
            if (!DBNull.Value.Equals(dr["OperID"]))
            {
                model.OperID = int.Parse(dr["OperID"].ToString());
            }
            if (!DBNull.Value.Equals(dr["OperNote"]))
            {
                model.OperNote = dr["OperNote"].ToString();
            }
            if (!DBNull.Value.Equals(dr["DayTime"]))
            {
                model.DayTime = float.Parse(dr["DayTime"].ToString());
            }
            if (!DBNull.Value.Equals(dr["WeekTime"]))
            {
                model.WeekTime = float.Parse(dr["WeekTime"].ToString());
            }
            if (!DBNull.Value.Equals(dr["MonthTime"]))
            {
                model.WeekTime = float.Parse(dr["MonthTime"].ToString());
            }
            if (!DBNull.Value.Equals(dr["Remark"]))
            {
                model.Remark = dr["Remark"].ToString();
            }

            if (!DBNull.Value.Equals(dr["UpdateTime"]))
            {
                model.UpdateTime = Convert.ToDateTime(dr["UpdateTime"]);
            }
            if (!DBNull.Value.Equals(dr["UpdateBy"]))
            {
                model.UpdateBy = dr["UpdateBy"].ToString();
            }
        }
          
    }
}
